import datetime
import json

import pandas as pd

from work_zykj.mysql import mysql_util

if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/埋点数据.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path)
    # 打印读取到的数据
    # print(df)
    print(type(df))
    utm_souce_list = []
    action_name_list = []
    event_time_list = []
    appid_list = []
    uid_list = []
    pv_list = []
    nick_list = []
    mobile_list = []
    for index, row in df.iterrows():
        # print(row['push_content'])
        utm_souce = row['utm_souce']
        action_name = row['action_name']
        event_time = row['event_time']
        appid = row['appid']
        uid = row['uid']
        pv = row['pv']
        nick = ''
        mobile = ''

        # print('%s-%s' % (org_code, order_id))
        sql = "select name from pc_permission a where a.event_tracking = '{}'".format(utm_souce)
        rs = mysql_util.select_by_sql(sql)
        rs = json.loads(rs)
        if len(rs) > 0:
            action_name = rs[0]['name']
        sql = "select * from pc_user u where u.id = '{}'".format(uid)
        rs = mysql_util.select_by_sql(sql)
        rs = json.loads(rs)
        if len(rs) > 0:
            nick = rs[0]['nick']
            mobile = rs[0]['mobile']
        utm_souce_list.append(utm_souce)
        action_name_list.append(action_name)
        event_time_list.append(event_time)
        uid_list.append(uid)
        nick_list.append(nick)
        mobile_list.append(mobile)
        pv_list.append(pv)

    # 导出excel
    excel_data = {}
    excel_data['utm_souce'] = utm_souce_list
    excel_data['action_name'] = action_name_list
    excel_data['event_time'] = event_time_list
    excel_data['uid'] = uid_list
    excel_data['nick'] = nick_list
    excel_data['mobile'] = mobile_list
    excel_data['pv'] = pv_list

    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + '埋点数据_{}.xlsx'.format(datetime.datetime.now().strftime('%Y%m%d%H%M%S')), index=False)
